package Database;

import java.sql.*;
import java.util.ArrayList;

import Model.Customer;

public class DBCustomer implements IFDBCustomer {
    private Connection con;

    public DBCustomer() {
	con = DbConnection.getInstance().getDBcon();
    }

    // Implements the methods from the interface
    // get all employees
    public Customer findCustomer(int id) {
	String wClause = "  id = '" + id + "'";

	return singleWhere(wClause);
    }

    public Customer searchCustomerName(String attValue) {
	String wClause = "name like '%" + attValue + "%'";
	System.out.println("SearchCustomer " + wClause);
	return singleWhere(wClause);
    }

    private Customer singleWhere(String wClause) {
	ResultSet results = null;

	String query = buildQuery(wClause);
	System.out.println(query);
	try { // read the Customer from the database
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    results = stmt.executeQuery(query);

	}// end try
	catch (Exception e) {
	    System.out.println("Query exception: " + e);
	}
	return buildCustomer(results);
    }

    private String buildQuery(String wClause) {
	String query = "SELECT name, caddress, postalCode, city, phone, ctype, id FROM customer";

	if (wClause.length() > 0)
	    query = query + " where" + wClause;

	return query;
    }

    public int insertCustomer(Customer cus) throws Exception { // call to get
							       // the next ssn
							       // number

	int rc = -1;
	String query = "INSERT INTO Customer(name, caddress, postalCode, city, phone, ctype, id)  VALUES('" + cus.getName() + "','" + cus.getAddress() + "','" + cus.getZip() + "','" + cus.getCity() + "'," + cus.getPhone() + ",'" + cus.getType() + "'," + cus.getId() + ");";

	System.out.println("insert : " + query);
	try { // insert new employee + dependent
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    rc = stmt.executeUpdate(query);
	    stmt.close();
	}// end try
	catch (SQLException ex) {
	    System.out.println("Customer ikke oprettet");
	    throw new Exception("Customer is not inserted correct");
	}
	return (rc);
    }

    private Customer buildCustomer(ResultSet results) {
	Customer customer = new Customer();

	try {
	    while (results.next()) {
		customer.setName(results.getString("name"));

		customer.setAddress(results.getString("caddress"));
		customer.setZip(results.getString("postalCode"));
		customer.setCity(results.getString("city"));
		customer.setPhone(results.getLong("phone"));
		customer.setType(results.getString("ctype"));
		customer.setId(results.getInt("Id"));
	    }

	} catch (Exception e) {
	    e.printStackTrace();
	}

	return customer;

    }

    public int deleteCustomer(int id) {
	int rc = -1;
	PreparedStatement pstmt = null;
	String delete = "delete from Customer where id = ?";
	System.out.println(delete);
	try {
	    pstmt = con.prepareStatement(delete);
	    pstmt.setInt(1, id);
	    rc = pstmt.executeUpdate();
	} catch (SQLException sqlE) {
	    System.out.println("SQL Error");
	    System.out.println(sqlE.getMessage());
	} catch (Exception e) {
	    e.getMessage();

	}
	return rc;
    }

    public int updateCustomer(Customer cus) {
	Customer customer = cus;
	int rc = -1;

	String query = "UPDATE customer SET " + "name ='" + customer.getName() + "', " + "caddress ='" + customer.getAddress() + "', " + "postalCode='" + customer.getZip() + "', " + "city ='" + customer.getCity() + "', " + "phone=" + customer.getPhone() + ", " + "ctype ='" + customer.getType() + "' " + " WHERE id = " + customer.getId() + "";
	System.out.println("Update query:" + query);
	try { // update employee
	    Statement stmt = con.createStatement();
	    stmt.setQueryTimeout(5);
	    rc = stmt.executeUpdate(query);

	    stmt.close();
	}// slut try
	catch (Exception ex) {
	    System.out.println("Update exception in customer db: " + ex);
	}
	return (rc);
    }

    @Override
    public Customer searchCustomerSsn(String id, boolean b) {
	// TODO Auto-generated method stub
	return null;
    }

}
